//201902104003邵潇漩
package bysj.dao;

import bysj.domain.Department;
import bysj.domain.School;
import bysj.service.SchoolService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;
/**
 * @author 邵潇漩
 * @version 1.0  11-13-2020
 */


public final class DepartmentDao {
    private static DepartmentDao departmentDao = null;

    private DepartmentDao() {
    }

    public static DepartmentDao getInstance() {
        if (departmentDao == null){
            departmentDao = new DepartmentDao();
        }
        return departmentDao;
    }

    public static Collection<Department> findAll(Pagination pagination, List<Condition> conditionList,Connection connection) throws SQLException {
        Collection<Department> departments = new TreeSet<Department>();
        int totalNum = CommonDao.getInstance().count(connection,"department");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM department");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            departments.add(new Department(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    SchoolService.getInstance().find(resultSet.getInt("school_id"))
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return departments;
    }

    public Department find(Integer id,Connection conn) throws SQLException {
        Department desiredDepartment = null;
        //创建查询的主语句
        String sql_select_id="SELECT*FROM department WHERE id=?";
        //获得查询的预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String description=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int schoolId=resultSet.getInt("school_id");
            School school= SchoolService.getInstance().find(schoolId);
            desiredDepartment=new Department(id,description,no,remarks,school);
        }
        JdbcHelper.close(resultSet,pstmt);
        return desiredDepartment;
    }

    public boolean update(Department department,Connection conn) throws SQLException {
        //创建sql语句
        String updateSchool_sql = "UPDATE department SET description=?, no=?, remarks=?, school_id=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateSchool_sql);
        // 为预编译参数赋值
        pstmt.setString(1,department.getDescription());
        pstmt.setString(2,department.getNo());
        pstmt.setString(3,department.getRemarks());
        pstmt.setInt(4,department.getSchool().getId());
        pstmt.setInt(5,department.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
        //返回department对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(Department department,Connection conn) throws SQLException {
        String addDepartment_sql="INSERT INTO department "+"(description,no,remarks,school_id)value(?,?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addDepartment_sql);
        //为预编译参数赋值
        pstmt.setString(1, department.getDescription());
        pstmt.setString(2, department.getNo());
        pstmt.setString(3, department.getRemarks());
        pstmt.setInt(4,department.getSchool().getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int rowAffected =pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowAffected>0;
    }

    public boolean delete(Integer id,Connection conn) throws SQLException {
        //创建sql语句
        String deleteDepartment_sql = "DELETE FROM department WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteDepartment_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }

    public int countAll(School school,Connection conn) throws SQLException{
        String str_sql="SELECT COUNT(id) as cnt_by_school_id FROM department WHERE school_id=?";
        PreparedStatement pstmt=conn.prepareStatement(str_sql);
        //为预编译参数赋值
        pstmt.setInt(1, school.getId());
        ResultSet resultSet=pstmt.executeQuery();
        int count=0;
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,pstmt);
        return count;
    }
    //public static void main(String[] args) throws SQLException{
        //Connection connection=JdbcHelper.getConn();
        //从数据库中读取id=2的学院记录，转换为内存对象
        //School mgtSch=SchoolDao.getInstance().find(2);
        //System.out.println(mgtSch);
        //在内存中创建misDept和pmDept
        //Department misDept=new Department("信息管理","0202","",mgtSch);
        //Department pmDept=new Department("工程管理","0201","",mgtSch);
        //Department misDept1=new Department("工程造价","0203","",mgtSch);
        //Department pmDept1=new Department("工业工程","0204","",mgtSch);
        //将内存对象pmDept和misDept保存到数据库表中
        //DepartmentDao.getInstance().add(pmDept);
        //DepartmentDao.getInstance().add(misDept);
        //DepartmentDao.getInstance().add(misDept1);
        //DepartmentDao.getInstance().add(pmDept1);
        //System.out.println(DepartmentDao.getInstance().findAll(connection));
        // misDept= DepartmentService.getInstance().find(1);
        //System.out.println(misDept);
        // DepartmentDao.getInstance().delete(2);
        //Collection<Department> departments=DepartmentDao.getInstance().findAll();
        //System.out.println(departments);
    //}
}

